#!/bin/bash

scriptPath=$(dirname $0);

attach="attach 'match.sqlite' as match;"

# -- data
query="SELECT sum(eventName='EVENT_TYPE_DATAOBJECT_RECEIVED' AND substr(nodeId,1,4)='peer') as received, sum(eventName='INCOMING_REJECT') as rejected, m.cnt, m.avgRatio, m.maxRatio, m.minRatio FROM events as e LEFT JOIN match.viewRelevance as m ON substr(e.dataobjectId,1,40)=m.dataobjectId WHERE eventName='INCOMING_REJECT' or (eventNAme='EVENT_TYPE_DATAOBJECT_RECEIVED' AND substr(nodeId,1,4)='peer') group by substr(e.dataobjectID,1,40) ORDER BY rejected desc;"
sqlite3 -batch -separator ' ' trace.sqlite "$attach $query" > tmp-reject


# CREATE VIEW viewRelevance AS SELECT dataobjectId, count(*) as cnt, avg(ratio) as avgRatio, max(ratio) as maxRatio, min(ratio) as minRatio, sum(ratio) as sumRatio FROM match WHERE isNodeDescription=0 and isOwnData=0 and ratio>=10 GROUP by dataobjectId;